<?php
if (!defined("BASEPATH")) exit("No direct script access allowed");

class User extends MY_Model {

	function __construct() {
		parent::__construct();
		$this->tableName = "user";
		$this->prefix = "usr";
	}

	function existsUser($where) {
		$this->db->select("usr_id");
		$this->db->from($this->tableName);
		$this->db->where($where);
		$result = $this->db->get();
		if(!$result->num_rows()) {
			return FALSE;
		}
		return $result->row();
	}

	function getUser($where = NULL, $start = NULL, $limit = NULL, $fields = NULL, $query = NULL, $order = "usrEmail", $dir = "ASC", $withUsr2Ent = FALSE) {

		$fieldsQuery = Common::fieldsquery($fields, $query);
		$order = Common::coalesce(array($order, "usrEmail"));
		$dir = Common::coalesce(array($dir, "ASC"));

		if(is_array($this->session->userdata("grpName")) and Common::isAdmin()) {
			$this->load->model("usr2ent");
			$getUsr2ent = $this->usr2ent->getUsr2ent("u2e_usr_id = '".$this->session->userdata("usrId")."'");
			if(!isset($getUsr2ent["INS"])) return array();
		}

		$this->db->select("usr_id, usr_email, usr_email_new, usr_code, usr_code_new, usr_verify, usr_registered, usr_lastupd, usr_password, usr_newsletter, usr_logged, usr_provider, u2g_grp_id, grp_name, group_concat(distinct(ifnull(u2e_cou_id, '')) separator ',') as couList, group_concat(distinct(ifnull(u2e_city_id, '')) separator ',') as cityList, group_concat(distinct(ifnull(u2e_ins_id, '')) separator ',') as insList, group_concat(distinct(ifnull(u2e_page_cat, '')) separator ',') as pageCatList,

		if(left(group_concat(distinct(ifnull(cou_name, '')) order by cou_name separator ', '),1) = ',', substr(group_concat(distinct(ifnull(cou_name, '')) order by cou_name separator ', '),2), group_concat(distinct(ifnull(cou_name, '')) order by cou_name separator ', ')) as couNameList,

		if(left(group_concat(distinct(ifnull(city_name, '')) order by city_name separator ', '),1) = ',', substr(group_concat(distinct(ifnull(city_name, '')) order by city_name separator ', '),2), group_concat(distinct(ifnull(city_name, '')) order by city_name separator ', ')) as cityNameList,

		if(left(group_concat(distinct(ifnull(ins_name, '')) order by ins_name separator ', '),1) = ',', substr(group_concat(distinct(ifnull(ins_name, '')) order by ins_name separator ', '),2), group_concat(distinct(ifnull(ins_name, '')) order by ins_name separator ', ')) as insNameList,
		
		if(left(group_concat(distinct(ifnull(szo_lname, '')) order by szo_lname separator ', '),1) = ',', substr(group_concat(distinct(ifnull(szo_lname, '')) order by szo_lname separator ', '),2), group_concat(distinct(ifnull(szo_lname, '')) order by szo_lname separator ', ')) as pageCatNameList,

		u2g_ins_id, u2g_ins_name, u2g_ins_address, u2g_ins_address_raw, u2g_ins_website, u2g_city_id, u2g_postal_code,
		coalesce(e2c_int, e2c_text) as fullName

		", FALSE);
		$this->db->from($this->tableName);
		$this->db->join("usr2grp", "usr2grp.u2g_usr_id = user.usr_id and u2g_del = 0", "left");
		$this->db->join("group", "usr2grp.u2g_grp_id = group.grp_id and grp_del = 0", "left");
		$this->db->join("ent2cat", "ent2cat.e2c_ent_id = user.usr_id and e2c_entity = 'USER' and e2c_cat_id = '".$this->db->escape_str($this->config->item("categoryUSRFullname"))."' and e2c_del = 0", "left");
		$this->db->join("usr2ent", "u2e_del = 0 and (u2e_usr_id = usr_id or u2e_usr_id = e2c_ent_id)", "left");

		$this->db->join("country", "usr2ent.u2e_cou_id = country.cou_id and cou_del = 0", "left");
		$this->db->join("city", "usr2ent.u2e_city_id = city.city_id and city_del = 0", "left");
		$this->db->join("institute", "usr2ent.u2e_ins_id = institute.ins_id and ins_del = 0", "left");
		$this->db->join("szotar", "usr2ent.u2e_page_cat = szotar.szo_sname and szo_type = 'ENTITY' and szo_del = 0", "left");

		$this->db->group_by("usr_id");
		$this->db->where("usr_del = 0".((!is_null($where))?(" and ".$where):("")).(($fieldsQuery)?(" and (".$fieldsQuery.") "):("")));

		if(Common::isAdmin()) {
			//$this->db->where_in("u2e_ins_id", $getUsr2ent["INS"]["children"]);
			$this->db->where("(usr_id = '".$this->session->userdata("usrId")."' or ((u2e_ins_id in (".implode(",", $getUsr2ent["INS"]["children"]).") or u2e_page_cat is not null) and usr_createdby_grp_id = 2))");
		}

		$this->db->order_by(Common::underscore($order), $dir);
		if(is_numeric($start)) {
			$this->db->limit($limit, $start);
		}
		$result = $this->db->get();
		//Common::pr($this->db->last_query());
		
		if(!$result->num_rows()) {
			return FALSE;
		}
		$getUser = array();
		foreach($result->result() as $row) {
			$getUser[$row->usr_id]["usrId"] = $row->usr_id;
			$getUser[$row->usr_id]["usrEmail"] = $row->usr_email;
			$getUser[$row->usr_id]["usrPassword"] = $row->usr_password;
			$getUser[$row->usr_id]["usrNewsletter"] = $row->usr_newsletter;
			$getUser[$row->usr_id]["usrLogged"] = str_replace("-", ".", $row->usr_logged);
			$getUser[$row->usr_id]["usrRegistered"] = str_replace("-", ".", $row->usr_registered);
			$getUser[$row->usr_id]["usrLastupd"] = ((!$row->usr_verify)?(str_replace("-", ".", $row->usr_lastupd)):("-"));;
			$getUser[$row->usr_id]["usrVerify"] = $row->usr_verify;
			$getUser[$row->usr_id]["usrProvider"] = $row->usr_provider;			
			$getUser[$row->usr_id]["grpName"][$row->u2g_grp_id] = $row->grp_name;
			$getUser[$row->usr_id]["couList"] = str_replace(" ", "", $row->couList);
			$getUser[$row->usr_id]["cityList"] = str_replace(" ", "", $row->cityList);
			$getUser[$row->usr_id]["insList"] = str_replace(" ", "", $row->insList);
			$getUser[$row->usr_id]["pageCatList"] = str_replace(" ", "", $row->pageCatList);
			$getUser[$row->usr_id]["couNameList"] = $row->couNameList;
			$getUser[$row->usr_id]["cityNameList"] = $row->cityNameList;
			$getUser[$row->usr_id]["insNameList"] = $row->insNameList;
			$getUser[$row->usr_id]["pageCatNameList"] = $row->pageCatNameList;

			$getUser[$row->usr_id]["u2gInsId"] = $row->u2g_ins_id;
			$getUser[$row->usr_id]["u2gInsName"] = (($row->u2g_ins_name)?($row->u2g_ins_name." (".$this->lang->line("ADDRESS").": ".$row->u2g_ins_address.", ".$this->lang->line("WEBSITE").": ".anchor($row->u2g_ins_website, $row->u2g_ins_website, "onclick=\"window.open(this.href); return false;\"").")"):(""));

			if($row->u2g_ins_id) {
				$this->load->model("institute");
				$getInstitute = $this->institute->getInstitute($where = "ins_id = '".$this->db->escape_str($row->u2g_ins_id)."'");
				if(count($getInstitute)) {
					$getUser[$row->usr_id]["u2gInsName"] = $getInstitute[0]["insName"]." (".$getInstitute[0]["cityName"].")";
				} else {
					$getUser[$row->usr_id]["u2gInsId"] = NULL;
					$getUser[$row->usr_id]["u2gInsName"] = $this->lang->line("THE_INSTITUTION_DOES_NOT_EXIST");
				}
			} elseif($row->u2g_ins_name) {
				$this->load->model("institute");
				$getInstitute = $this->institute->getInstitute($where = "ins_name = '".$this->db->escape_str($row->u2g_ins_name)."'");
				if(count($getInstitute)) {
					$getUser[$row->usr_id]["u2gInsId"] = $getInstitute[0]["insId"];
					$getUser[$row->usr_id]["u2gInsName"] = $getInstitute[0]["insName"]." (".$getInstitute[0]["cityName"].")";
				}
			}
			
			$getUser[$row->usr_id]["u2gCityId"] = $row->u2g_city_id;
			$getUser[$row->usr_id]["u2gPostalCode"] = $row->u2g_postal_code;
			
			$getUser[$row->usr_id]["fullName"] = $row->fullName;
			$getUser[$row->usr_id]["usrEmailNew"] = $row->usr_email_new;
			$getUser[$row->usr_id]["usrCode"] = $row->usr_code;
			$getUser[$row->usr_id]["usrCodeNew"] = $row->usr_code_new;

			$getUser[$row->usr_id]["u2gInsNameRaw"] = $row->u2g_ins_name;
			$getUser[$row->usr_id]["u2gInsAddressRaw"] = $row->u2g_ins_address_raw;
			$getUser[$row->usr_id]["u2gInsWebRaw"] = $row->u2g_ins_website;
		}
		
		if($withUsr2Ent) {
			$this->load->model("usr2ent");
			foreach($getUser as $usrId => $usrData) {
				
			}
		}
		return $getUser;
	}

	function getUserCount($where = NULL, $fields, $query) {

		$fieldsQuery = Common::fieldsquery($fields, $query);

		if(is_array($this->session->userdata("grpName")) and Common::isAdmin()) {
			$this->load->model("usr2ent");
			$getUsr2ent = $this->usr2ent->getUsr2ent("u2e_usr_id = '".$this->session->userdata("usrId")."'");
			if(!isset($getUsr2ent["INS"])) return 0;
		}
		
		$qs =	"
					select
						count(distinct usr_id) as cnt
					from
						".$this->db->dbprefix.$this->tableName."
						left join ".$this->db->dbprefix."usr2grp on u2g_usr_id = usr_id and u2g_del = 0
						left join ".$this->db->dbprefix."usr2ent on u2e_usr_id = usr_id and u2e_del = 0
						left join ".$this->db->dbprefix."group on u2g_grp_id = grp_id and grp_del = 0
					where
						usr_del = 0  ".((is_array($this->session->userdata("grpName")) and Common::isAdmin())?(" and (usr_id = '".$this->session->userdata("usrId")."' or (u2e_ins_id in (".implode(",", $getUsr2ent["INS"]["children"]).") and usr_createdby_grp_id = 2))"):(""))." ".((!is_null($where))?(" and ".$where):(""))." ".(($fieldsQuery)?(" and (".$fieldsQuery.") "):(""))."
					group by
						usr_id
					";
		$result = $this->db->query($qs);

		if($result->num_rows()) {
			foreach ($result->result() as $row) {
				return $row->cnt;
			}
		}
	}

	function getSubscribers() {
		$this->db->select("usr_email");
		$this->db->from($this->tableName);
		$this->db->where("usr_del = 0 and usr_newsletter = 'I'");
		$result = $this->db->get();
		if(!$result->num_rows()) {
			return "";
		}
		$getSubscribers = "";
		foreach($result->result() as $row) {
			$getSubscribers .= (($getSubscribers)?(", "):("")).$row->usr_email;
		}
		return $getSubscribers;
	}
	
	function getSingleUser($where) {
		$this->db->select("usr_id, usr_email, coalesce(e2c_int, e2c_text) as fullName", FALSE);
		$this->db->from($this->tableName);
		$this->db->join("ent2cat", "ent2cat.e2c_ent_id = user.usr_id and e2c_entity = 'USER' and e2c_cat_id = '".$this->db->escape_str($this->config->item("categoryUSRFullname"))."' and e2c_del = 0", "left");
		$this->db->group_by("usr_id");
		$this->db->where("usr_del = 0".((!is_null($where))?(" and ".$where):("")));
		$result = $this->db->get();
		//Common::pr($this->db->last_query());
		
		if(!$result->num_rows()) {
			return FALSE;
		}
		$getUser = array();
		foreach($result->result() as $row) {
			$getUser[$row->usr_id]["usrEmail"] = $row->usr_email;
			$getUser[$row->usr_id]["fullName"] = $row->fullName;
		}
		return $getUser;
	}

}

?>